/**
 * 
 */
package com.hsd.app.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author shl
 *
 */
public class ExcelRw {
	private static Log log = LogFactory.getLog(ExcelRw.class);//��־������   
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String path = getFilePath("Book1.xls");
		File excel = getExcelFile(path);
		String[] fileds = {"c1","c2","c3"};
		//readExcel(excel,fileds,-1);
	}
	/**
	 * ����ļ�·����ȡҪ��ȡ���ļ�
	 * @param filePath
	 * @return
	 */
	public static File getExcelFile(String filePath){
		if(StringUtils.isNotBlank(filePath))
			return new File(filePath);
		return null; 
	}
	public static String getFilePath(String fileName){
		if(StringUtils.isNotBlank(fileName)){
			try {
				
				String rootPath = com.hsd.app.util.Utilities.getWebappRoot().getAbsolutePath();
				System.out.println("rootPath:" + rootPath);
				rootPath = rootPath.replace("\\", "/");
				return rootPath+"/upload/" + fileName;
			} catch (Exception e) { 
				e.printStackTrace();
			}
		}
		return null;
	}
	/**
	 * ��ȡexcel
	 * @param excelFile
	 * @param fileds
	 * @param sheetNum
	 * @return
	 */
	public static List readExcel(File excelFile,String[] fileds,int sheetNum){
		List resultList = new ArrayList();
		
		if(-1 == sheetNum)
			sheetNum = 0;
		Workbook book = null;
	    try {
	      book =
	          Workbook.getWorkbook(excelFile);
	    }
	    catch (BiffException ex) {
	    	log.error("Read Excel file failed!" + ex.getMessage()); 
	    }
	    catch (IOException ex) {
	    	log.error("Read Excel file failed!" + ex.getMessage()); 
	    }
	    log.debug("1");
        if(null != book){
        	try{
	         
	        	Sheet sheet = book.getSheet(sheetNum);
	        	int allColumns = sheet.getColumns();
	        	if(allColumns != fileds.length){
	        	 
	        	}
	        	int rowSize = sheet.getRows(); 
	        	int column = fileds.length;
	        	log.info("fileds:" + fileds.length);
	        	
	        	for(int k=0;k<fileds.length;k++){
	        		System.out.println("字段名称"+fileds[k]);
	        		
	        	}
	        	System.out.println("column:" + column);
	        	System.out.println("rowSize:" + rowSize);
	        	for(int i = 0;i<rowSize;i++){
	        		Map rowData = new HashMap();
	        		for(int  j =0;j<column;j++){
	        			Cell cell = sheet.getCell(j,i); 
	        			System.out.println("内容:"+i+"," + j +":" +fileds[j]+":" + cell.getContents()); 
	        			rowData.put(fileds[j], cell.getContents()); 
	        		} 
//	        		if(null == rowData.get("JILIANG") || "".equals(rowData.get("JILIANG")))
//	        			continue;
	        		resultList.add(rowData);
	        		System.out.println(rowData);
	        	}
	        	System.out.println("共读取到数据："+resultList.size()+"条");
        	}catch(Exception e){
        		log.info("异常信息:" + e.getMessage());
        		e.printStackTrace();
        	}finally{
        		if(null != book)
    	    		book.close(); 
        	}
       } 
        
		return resultList;
	}
	/**
	 * ��ȡexcel
	 * @param excelFile
	 * @param fileds
	 * @param sheetNum
	 * @return
	 */
	public static List readExcel1(File excelFile,String[] fileds,int sheetNum){
		List resultList = new ArrayList();
		
		if(-1 == sheetNum)
			sheetNum = 0;
		Workbook book = null;
	    try {
	      book =
	          Workbook.getWorkbook(excelFile);
	    }
	    catch (BiffException ex) {
	    	log.error("Read Excel file failed!" + ex.getMessage()); 
	    }
	    catch (IOException ex) {
	    	log.error("Read Excel file failed!" + ex.getMessage()); 
	    }
	   
        if(null != book){
        	try{
	        	//��ù����
	        	Sheet sheet = book.getSheet(sheetNum);
	        	int allColumns = sheet.getColumns();
	        	if(allColumns != fileds.length){ 
	        	}
	        	int rowSize = sheet.getRows(); 
	        	int column = fileds.length;
	        	for(int i = 1;i<rowSize;i++){
	        		Map rowData = new HashMap();
	        		for(int  j =0;j<column;j++){
	        			Cell cell = sheet.getCell(j,i); 
	        			log.info("数据内容:"+i+"," + j +":" +fileds[j]); 
	        			rowData.put(fileds[j], cell.getContents()); 
	        		}  
	        		resultList.add(rowData);
	        	}
	        	log.info("����ȡ��"+resultList.size() );
        	}catch(Exception e){
        		log.info("异常信息:" + e.getMessage());
        		e.printStackTrace();
        	}finally{
        		if(null != book)
    	    		book.close(); 
        	}
       } 
        log.debug("2");
		return resultList;
	}
	 /**
	  * create a new excelFile
	  * @param excelFileName create name
	  */
	 public static void createExcelFile(String excelFileName){
	  try{
	   WritableWorkbook wwb = Workbook.createWorkbook(new File(excelFileName));
	   WritableSheet     ws  = wwb.createSheet("sheet1",0);
	   //also,The first parameter is  column,the second parameter is row.
	   // add normal label data
	   Label label00 = new Label(0,0,"Label00");
	   ws.addCell(label00);
	   //add font formating data   
	   WritableFont  wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD , true);
	   WritableCellFormat wff = new WritableCellFormat(wf);
	   Label label10 = new Label(1,0,"Label10",wff);
	   ws.addCell(label10);
	   //add color font formating data
	   WritableFont wf_color = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.RED);
	   WritableCellFormat wff_color = new WritableCellFormat(wf_color);
	   wff_color.setBackground(Colour.GRAY_25); //set background coloe to gray  
	   Label label20 = new Label(2,0,"Label20",wff_color);   
	   ws.addCell(label20);
	   
	   //�ϲ���Ԫ��
	   WritableFont wf_merge = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.DOUBLE_ACCOUNTING,Colour.GREEN);
	   WritableCellFormat wff_merge = new WritableCellFormat(wf_merge);
	   wff_merge.setBackground(Colour.BLACK);
	   Label label30 = new Label(3,0,"Label30",wff_merge);   
	   ws.addCell(label30);
	   Label label40 = new Label(4,0,"Label40");
	   ws.addCell(label40);
	   Label label50 = new Label(5,0,"Label50");
	   ws.addCell(label50);
	     //�ϲ� (0,3) (4,0) 
	     //attention : ���ϲ�������в�Ϊ��,��ô�ͰѺ������������,��ʽҲ�ǰ�ǰһ��Ԫ��ĸ�ʽ 
	   ws.mergeCells(3,0,4,0); 
	   
	   //���Number��ʽ���
	   jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926);
	   ws.addCell(labelN);
	   
	   //��Ӵ���formatting��Number����
	   jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##");
	   jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
	   jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
	   ws.addCell(labelNF);
	   
	   //���Boolean���� 
	   jxl.write.Boolean labelBoolean = new jxl.write.Boolean(2,1,false);
	   ws.addCell(labelBoolean);
	   
	   //���DateTime���� 
	   DateTime labelDT = new DateTime(3,1,new Date());
	   ws.addCell(labelDT);
	   
	   //��Ӵ��и�ʽ��DataTime���
	   DateFormat dtf = new DateFormat("yyyy-MM-dd hh:mm:ss");
	   WritableCellFormat wcfDt = new WritableCellFormat(dtf);   
	   wcfDt.setBackground(Colour.YELLOW);
	   DateTime labelDT_format =  new DateTime(4,1,new java.util.Date(),wcfDt);
	   ws.addCell(labelDT_format);
	   ws.mergeCells(4,1,5,1); //�Ƚϳ�,��}��4��ʾ     
	   
	   wwb.write();
	   wwb.close();
	  }
	  catch(IOException e){
	   e.printStackTrace();
	  }
	  catch(WriteException e){
	   e.printStackTrace();
	  }  
	 }

	

}
